  'use strict';

  const Service = require('egg').Service;

  /**
   * 统计服务类
   * 处理人员相关的数据库操作和业务逻辑
   */
  class StatisticService extends Service {

    /**
     * 获取投稿统计信息 根据部门
     * 支持多维度筛选和排序
     * @param {Object} params - 查询参数
     * @param {string} [params.order_by='score'] - 排序方式：score-按分数，count-按数量
     * @param {string} [params.start_date] - 开始日期，格式：YYYY-MM-DD
     * @param {string} [params.end_date] - 结束日期，格式：YYYY-MM-DD
     * @param {number} [params.approved_status=1] - 审核状态
     * @returns {Promise<Array>} 统计结果列表
     */
    async getAllContributionStatisticsByDepartment(params) {
      const {
        order_by = 'score',
          start_date,
          end_date,
          approved_status = 1
      } = params;

      // 构建贡献表的筛选条件
      let contributionConditions = ['isDeleted = 0'];
      const queryParams = [];

      if (start_date) {
        contributionConditions.push('created_at >= ?');
        queryParams.push(`${start_date} 00:00:00`);
      }

      if (end_date) {
        contributionConditions.push('created_at <= ?');
        queryParams.push(`${end_date} 23:59:59`);
      }

      // 添加审核状态条件
      if (approved_status !== undefined) {
        contributionConditions.push('approved_status = ?');
        queryParams.push(approved_status);
      }

      const sql = `
        SELECT 
            d.department_id AS 'department_id',
            COALESCE(d.department_name, '无对应投稿部门') AS 'department_name',
            -- 统计作者1的条数
            COUNT(CASE WHEN c.author1 IS NOT NULL AND c.isDeleted = 0 AND p1.is_deleted = 0 AND d.is_deleted = 0 THEN 1 END) AS 'author1_count',
            -- 统计作者1的分数总和
            SUM(CASE WHEN c.author1 IS NOT NULL AND c.isDeleted = 0 AND p1.is_deleted = 0 AND d.is_deleted = 0 THEN c.author1_score END) AS 'author1_total_score',
            -- 统计作者2的条数
            COUNT(CASE WHEN c.author2 IS NOT NULL AND c.isDeleted = 0 AND p2.is_deleted = 0 AND d.is_deleted = 0 THEN 1 END) AS 'author2_count',
            -- 统计作者2的分数总和
            SUM(CASE WHEN c.author2 IS NOT NULL AND c.isDeleted = 0 AND p2.is_deleted = 0 AND d.is_deleted = 0 THEN c.author2_score END) AS 'author2_total_score',
            -- 统计作者3的条数
            COUNT(CASE WHEN c.author3 IS NOT NULL AND c.isDeleted = 0 AND p3.is_deleted = 0 AND d.is_deleted = 0 THEN 1 END) AS 'author3_count',
            -- 统计作者3的分数总和
            SUM(CASE WHEN c.author3 IS NOT NULL AND c.isDeleted = 0 AND p3.is_deleted = 0 AND d.is_deleted = 0 THEN c.author3_score END) AS 'author3_total_score',
            -- 统计总投稿条数（只要有投稿记录就算一条，这里简单以非空的投稿ID来统计）
            COUNT(CASE WHEN c.contribution_id IS NOT NULL AND c.isDeleted = 0 THEN 1 END) AS 'total_count',
            -- 统计总分数（将所有作者分数相加，可根据实际情况调整是否包含额外加分等情况）
            SUM(CASE WHEN c.isDeleted = 0 THEN COALESCE(c.author1_score, 0) + COALESCE(c.author2_score, 0) + COALESCE(c.author3_score, 0) END) AS 'total_score'
        FROM 
            tb_department d
        -- 使用左连接，确保即使没有投稿相关记录的部门也能出现在结果中
        LEFT JOIN 
            (
                SELECT 
                    author1,
                    author1_score,
                    author2,
                    author2_score,
                    author3,
                    author3_score,
                    contribution_id,
                    isDeleted,
                    submission_date
                FROM 
                    tb_contribution
                WHERE 
                    ${contributionConditions.join(' AND ')}
            ) c ON (
                (c.author1 IS NOT NULL AND EXISTS (SELECT 1 FROM tb_person p WHERE p.person_id = c.author1 AND p.department_id = d.department_id AND p.is_deleted = 0))
                OR (c.author2 IS NOT NULL AND EXISTS (SELECT 1 FROM tb_person p WHERE p.person_id = c.author2 AND p.department_id = d.department_id AND p.is_deleted = 0))
                OR (c.author3 IS NOT NULL AND EXISTS (SELECT 1 FROM tb_person p WHERE p.person_id = c.author3 AND p.department_id = d.department_id AND p.is_deleted = 0))
            )
        LEFT JOIN 
            tb_person p1 ON c.author1 = p1.person_id AND p1.is_deleted = 0
        LEFT JOIN 
            tb_person p2 ON c.author2 = p2.person_id AND p2.is_deleted = 0
        LEFT JOIN 
            tb_person p3 ON c.author3 = p3.person_id AND p3.is_deleted = 0
        WHERE 
            d.is_deleted = 0
        GROUP BY 
            d.department_id, d.department_name
        ORDER BY 
            total_${order_by} DESC;`;

      const result = await this.app.mysql.query(sql, queryParams);

      // 处理数据，格式化分数
      return result;
    }

    /**
     * 获取投稿统计信息 根据项目组
     * 支持多维度筛选和排序
     * @param {Object} params - 查询参数
     * @param {string} [params.order_by='score'] - 排序方式：score-按分数，count-按数量
     * @param {string} [params.start_date] - 开始日期，格式：YYYY-MM-DD
     * @param {string} [params.end_date] - 结束日期，格式：YYYY-MM-DD
     * @param {number} [params.approved_status=1] - 审核状态
     * @returns {Promise<Array>} 统计结果列表
     */
    async getAllContributionStatisticsByProjectGroup(params) {
      const {
        order_by = 'score',
          start_date,
          end_date,
          approved_status = 1
      } = params;

      // 构建贡献表的筛选条件
      let contributionConditions = ['isDeleted = 0'];
      const queryParams = [];

      if (start_date) {
        contributionConditions.push('created_at >= ?');
        queryParams.push(`${start_date} 00:00:00`);
      }

      if (end_date) {
        contributionConditions.push('created_at <= ?');
        queryParams.push(`${end_date} 23:59:59`);
      }

      // 添加审核状态条件
      if (approved_status !== undefined) {
        contributionConditions.push('approved_status = ?');
        queryParams.push(approved_status);
      }

      const sql = `
            SELECT 
                pg.project_group_name AS 'project_group_name',
                -- 统计每个项目组的投稿数量
                COUNT(c.contribution_id) AS 'total_count',
                -- 统计每个项目组的总分数（将所有作者分数相加，可根据实际情况调整是否包含额外加分等情况）
                SUM(COALESCE(c.author1_score, 0) + COALESCE(c.author2_score, 0) + COALESCE(c.author3_score, 0)) AS 'total_score'
            FROM 
                tb_project_group pg
            -- 使用左连接获取项目组对应的投稿信息，确保即使项目组没有投稿也能显示在结果中
            LEFT JOIN 
                tb_contribution c ON pg.project_group_id = c.project_group_id
                    AND 
                    ${contributionConditions.join(' AND ')}
            GROUP BY 
                pg.project_group_name
            ORDER BY 
                total_${order_by} DESC;`;

      const result = await this.app.mysql.query(sql, queryParams);

      // 处理数据，格式化分数
      return result;
    }

  }

  module.exports = StatisticService;